/*
 * ==================================================================== Licensed
 * to the Apache Software Foundation (ASF) under one or more contributor license
 * agreements. See the NOTICE file distributed with this work for additional
 * information regarding copyright ownership. The ASF licenses this file to You
 * under the Apache License, Version 2.0 (the "License"); you may not use this
 * file except in compliance with the License. You may obtain a copy of the
 * License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by
 * applicable law or agreed to in writing, software distributed under the
 * License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS
 * OF ANY KIND, either express or implied. See the License for the specific
 * language governing permissions and limitations under the License.
 * ====================================================================
 */
package org.apache.poi.xssf.usermodel;

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.FormulaParser;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName;

/**
 * Represents a defined named range in a SpreadsheetML workbook.
 * <p>
 * Defined names are descriptive text that is used to represents a cell, range
 * of cells, formula, or constant value. Use easy-to-understand names, such as
 * Products, to refer to hard to understand ranges, such as
 * <code>Sales!C20:C30</code>.
 * </p>
 * Example:
 *
 * <pre>
 * <blockquote>
 *   XSSFWorkbook wb = new XSSFWorkbook();
 *   XSSFSheet sh = wb.createSheet("Sheet1");
 *
 *   //applies to the entire workbook
 *   XSSFName name1 = wb.createName();
 *   name1.setNameName("FMLA");
 *   name1.setRefersToFormula("Sheet1!$B$3");
 *
 *   //applies to Sheet1
 *   XSSFName name2 = wb.createName();
 *   name2.setNameName("SheetLevelName");
 *   name2.setComment("This name is scoped to Sheet1");
 *   name2.setLocalSheetId(0);
 *   name2.setRefersToFormula("Sheet1!$B$3");
 *
 * </blockquote>
 * </pre>
 *
 * @author Nick Burch
 * @author Yegor Kozlov
 */
public final class XSSFName implements Name {

    /**
     * A built-in defined name that specifies the workbook's print area
     */
    public static final String BUILTIN_PRINT_AREA = "_xlnm.Print_Area";

    /**
     * A built-in defined name that specifies the row(s) or column(s) to repeat
     * at the top of each printed page.
     */
    public static final String BUILTIN_PRINT_TITLE = "_xlnm.Print_Titles";

    /**
     * A built-in defined name that refers to a range containing the criteria
     * values to be used in applying an advanced filter to a range of data
     */
    public static final String BUILTIN_CRITERIA = "_xlnm.Criteria:";

    /**
     * this defined name refers to the range containing the filtered output
     * values resulting from applying an advanced filter criteria to a source
     * range
     */
    public static final String BUILTIN_EXTRACT = "_xlnm.Extract:";

    /**
     * ?an be one of the following
     * <li>this defined name refers to a range to which an advanced filter has
     * been applied. This represents the source data range, unfiltered.
     * <li>This defined name refers to a range to which an AutoFilter has been
     * applied
     */
    public static final String BUILTIN_FILTER_DB = "_xlnm._FilterDatabase";

    /**
     * A built-in defined name that refers to a consolidation area
     */
    public static final String BUILTIN_CONSOLIDATE_AREA = "_xlnm.Consolidate_Area";

    /**
     * A built-in defined name that specified that the range specified is from a
     * database data source
     */
    public static final String BUILTIN_DATABASE = "_xlnm.Database";

    /**
     * A built-in defined name that refers to a sheet title.
     */
    public static final String BUILTIN_SHEET_TITLE = "_xlnm.Sheet_Title";

    private final XSSFWorkbook _workbook;
    private final CTDefinedName _ctName;

    /**
     * Creates an XSSFName object - called internally by XSSFWorkbook.
     *
     * @param name - the xml bean that holds data represenring this defined
     *        name.
     * @param workbook - the workbook object associated with the name
     * @see org.apache.poi.xssf.usermodel.XSSFWorkbook#createName()
     */
    protected XSSFName(CTDefinedName name, XSSFWorkbook workbook) {
        this._workbook = workbook;
        this._ctName = name;
    }

    /**
     * Returns the underlying named range object
     */
    protected CTDefinedName getCTName() {
        return this._ctName;
    }

    /**
     * Returns the name that will appear in the user interface for the defined
     * name.
     *
     * @return text name of this defined name
     */
    @Override
    public String getNameName() {
        return this._ctName.getName();
    }

    /**
     * Sets the name that will appear in the user interface for the defined
     * name. Names must begin with a letter or underscore, not contain spaces
     * and be unique across the workbook.
     * <p>
     * A name must always be unique within its scope. POI prevents you from
     * defining a name that is not unique within its scope. However you can use
     * the same name in different scopes. Example:
     *
     * <pre>
     * <blockquote>
     * //by default names are workbook-global
     * XSSFName name;
     * name = workbook.createName();
     * name.setNameName("sales_08");
     *
     * name = workbook.createName();
     * name.setNameName("sales_08"); //will throw an exception: "The workbook already contains this name (case-insensitive)"
     *
     * //create sheet-level name
     * name = workbook.createName();
     * name.setSheetIndex(0); //the scope of the name is the first sheet
     * name.setNameName("sales_08");  //ok
     *
     * name = workbook.createName();
     * name.setSheetIndex(0);
     * name.setNameName("sales_08");  //will throw an exception: "The sheet already contains this name (case-insensitive)"
     *
     * </blockquote>
     * </pre>
     * </p>
     *
     * @param name name of this defined name
     * @throws IllegalArgumentException if the name is invalid or the workbook
     *         already contains this name (case-insensitive)
     */
    @Override
    public void setNameName(String name) {
        // XSSFName.validateName(name);

        final String oldName = this.getNameName();
        final int sheetIndex = this.getSheetIndex();
        //Check to ensure no other names have the same case-insensitive name at the same scope
        for (final XSSFName foundName : this._workbook.getNames(name)) {
            if ((foundName.getSheetIndex() == sheetIndex) && (foundName != this)) {
                final String msg =
                        "The " + (sheetIndex == -1 ? "workbook" : "sheet") + " already contains this name: " + name;
                throw new IllegalArgumentException(msg);
            }
        }
        this._ctName.setName(name);
        //Need to update the name -> named ranges map
        this._workbook.updateName(this, oldName);
    }

    @Override
    public String getRefersToFormula() {
        final String result = this._ctName.getStringValue();
        if ((result == null) || (result.length() < 1)) {
            return null;
        }
        return result;
    }

    @Override
    public void setRefersToFormula(String formulaText) {
        final XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(this._workbook);
        //validate through the FormulaParser
        FormulaParser.parse(formulaText, fpb, FormulaType.NAMEDRANGE, this.getSheetIndex(), -1);

        this._ctName.setStringValue(formulaText);
    }

    @Override
    public boolean isDeleted() {
        final String formulaText = this.getRefersToFormula();
        if (formulaText == null) {
            return false;
        }
        final XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(this._workbook);
        final Ptg[] ptgs = FormulaParser.parse(formulaText, fpb, FormulaType.NAMEDRANGE, this.getSheetIndex(), -1);
        return Ptg.doesFormulaReferToDeletedCell(ptgs);
    }

    /**
     * Tell Excel that this name applies to the worksheet with the specified
     * index instead of the entire workbook.
     *
     * @param index the sheet index this name applies to, -1 unsets this
     *        property making the name workbook-global
     */
    @Override
    public void setSheetIndex(int index) {
        final int lastSheetIx = this._workbook.getNumberOfSheets() - 1;
        if ((index < -1) || (index > lastSheetIx)) {
            throw new IllegalArgumentException("Sheet index (" + index + ") is out of range" +
                    (lastSheetIx == -1 ? "" : (" (0.." + lastSheetIx + ")")));
        }

        if (index == -1) {
            if (this._ctName.isSetLocalSheetId()) {
                this._ctName.unsetLocalSheetId();
            }
        } else {
            this._ctName.setLocalSheetId(index);
        }
    }

    /**
     * Returns the sheet index this name applies to.
     *
     * @return the sheet index this name applies to, -1 if this name applies to
     *         the entire workbook
     */
    @Override
    public int getSheetIndex() {
        return this._ctName.isSetLocalSheetId() ? (int) this._ctName.getLocalSheetId() : -1;
    }

    /**
     * Indicates that the defined name refers to a user-defined function. This
     * attribute is used when there is an add-in or other code project
     * associated with the file.
     *
     * @param value <code>true</code> indicates the name refers to a function.
     */
    @Override
    public void setFunction(boolean value) {
        this._ctName.setFunction(value);
    }

    /**
     * Indicates that the defined name refers to a user-defined function. This
     * attribute is used when there is an add-in or other code project
     * associated with the file.
     *
     * @return <code>true</code> indicates the name refers to a function.
     */
    public boolean getFunction() {
        return this._ctName.getFunction();
    }

    /**
     * Specifies the function group index if the defined name refers to a
     * function. The function group defines the general category for the
     * function. This attribute is used when there is an add-in or other code
     * project associated with the file.
     *
     * @param functionGroupId the function group index that defines the general
     *        category for the function
     */
    public void setFunctionGroupId(int functionGroupId) {
        this._ctName.setFunctionGroupId(functionGroupId);
    }

    /**
     * Returns the function group index if the defined name refers to a
     * function. The function group defines the general category for the
     * function. This attribute is used when there is an add-in or other code
     * project associated with the file.
     *
     * @return the function group index that defines the general category for
     *         the function
     */
    public int getFunctionGroupId() {
        return (int) this._ctName.getFunctionGroupId();
    }

    /**
     * Get the sheets name which this named range is referenced to
     *
     * @return sheet name, which this named range referred to. Empty string if
     *         the referenced sheet name was not found.
     */
    @Override
    public String getSheetName() {
        if (this._ctName.isSetLocalSheetId()) {
            // Given as explicit sheet id
            final int sheetId = (int) this._ctName.getLocalSheetId();
            return this._workbook.getSheetName(sheetId);
        }
        final String ref = this.getRefersToFormula();
        final AreaReference areaRef = new AreaReference(ref, SpreadsheetVersion.EXCEL2007);
        return areaRef.getFirstCell().getSheetName();
    }

    /**
     * Is the name refers to a user-defined function ?
     *
     * @return <code>true</code> if this name refers to a user-defined function
     */
    @Override
    public boolean isFunctionName() {
        return this.getFunction();
    }

    /**
     * Returns the comment the user provided when the name was created.
     *
     * @return the user comment for this named range
     */
    @Override
    public String getComment() {
        return this._ctName.getComment();
    }

    /**
     * Specifies the comment the user provided when the name was created.
     *
     * @param comment the user comment for this named range
     */
    @Override
    public void setComment(String comment) {
        this._ctName.setComment(comment);
    }

    @Override
    public int hashCode() {
        return this._ctName.toString().hashCode();
    }

    /**
     * Compares this name to the specified object. The result is
     * <code>true</code> if the argument is XSSFName and the underlying
     * CTDefinedName bean equals to the CTDefinedName representing this name
     *
     * @param o the object to compare this <code>XSSFName</code> against.
     * @return <code>true</code> if the <code>XSSFName </code>are equal;
     *         <code>false</code> otherwise.
     */
    @Override
    public boolean equals(Object o) {
        if (o == this) {
            return true;
        }

        if (!(o instanceof XSSFName)) {
            return false;
        }

        final XSSFName cf = (XSSFName) o;
        return this._ctName.toString().equals(cf.getCTName().toString());
    }

    /**
     * https://support.office.com/en-us/article/Define-and-use-names-in-formulas-4D0F13AC-53B7-422E-AFD2-ABD7FF379C64#bmsyntax_rules_for_names
     * Valid characters: First character: { letter | underscore | backslash }
     * Remaining characters: { letter | number | period | underscore } Cell
     * shorthand: cannot be { "C" | "c" | "R" | "r" } Cell references
     * disallowed: cannot be a cell reference $A$1 or R1C1 Spaces are not valid
     * (follows from valid characters above) Name length: (XSSF-specific?) 255
     * characters maximum Case sensitivity: all names are case-insensitive
     * Uniqueness: must be unique (for names with the same scope)
     *
     * @param name
     */
    @SuppressWarnings("unused")
    private static void validateName(String name) {

        if (name.length() == 0) {
            throw new IllegalArgumentException("Name cannot be blank");
        }
        if (name.length() > 255) {
            throw new IllegalArgumentException("Invalid name: '" + name + "': cannot exceed 255 characters in length");
        }
        if (name.equalsIgnoreCase("R") || name.equalsIgnoreCase("C")) {
            throw new IllegalArgumentException("Invalid name: '" + name + "': cannot be special shorthand R or C");
        }

        // is first character valid?
        final char c = name.charAt(0);
        String allowedSymbols = "_\\";
        boolean characterIsValid = (Character.isLetter(c) || (allowedSymbols.indexOf(c) != -1));
        if (!characterIsValid) {
            throw new IllegalArgumentException(
                    "Invalid name: '" + name + "': first character must be underscore or a letter");
        }

        // are all other characters valid?
        allowedSymbols = "_.\\"; //backslashes needed for unicode escape
        for (final char ch : name.toCharArray()) {
            characterIsValid = (Character.isLetterOrDigit(ch) || (allowedSymbols.indexOf(ch) != -1));
            if (!characterIsValid) {
                throw new IllegalArgumentException(
                        "Invalid name: '" + name + "': name must be letter, digit, period, or underscore");
            }
        }

        // Is the name a valid $A$1 cell reference
        // Because $, :, and ! are disallowed characters, A1-style references become just a letter-number combination
        if (name.matches("[A-Za-z]+\\d+")) {
            final String col = name.replaceAll("\\d", "");
            final String row = name.replaceAll("[A-Za-z]", "");

            try {
                if (CellReference.cellReferenceIsWithinRange(col, row, SpreadsheetVersion.EXCEL2007)) {
                    throw new IllegalArgumentException(
                            "Invalid name: '" + name + "': cannot be $A$1-style cell reference");
                }
            } catch (final NumberFormatException e) {
                // row was not parseable as an Integer, such as a BigInt
                // therefore name passes the not-a-cell-reference criteria
            }
        }

        // Is the name a valid R1C1 cell reference?
        if (name.matches("[Rr]\\d+[Cc]\\d+")) {
            throw new IllegalArgumentException("Invalid name: '" + name + "': cannot be R1C1-style cell reference");
        }
    }
}
